package task.www.lagou.app;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import task.www.lagou.entity.Employee;
import task.www.lagou.utils.DruidUtils;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

/**
 * 需求1: 查询所有的员工信息 (不包含没有部门的员工)。
 *
 * 需求2: 查询每个员工的 姓名, 薪资 和 所属部门名称
 */
public class TestQuestion3 {
    public static void main(String[] args) {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        try {
            String sql1 = "SELECT * FROM employee e,dept d WHERE e.id = d.id AND did IS NOT NULL";
            List<Employee> list1 = qr.query(sql1,new BeanListHandler<Employee>(Employee.class));
            System.out.println("需求1: 查询所有的员工信息 (不包含没有部门的员工)。");
            for(Employee e : list1){
                System.out.println(e);
            }

            String sql2 = "SELECT NAME,salary,deptname FROM employee e LEFT OUTER JOIN dept d ON e.id = d.id;";
            List<Object[]> list2 = qr.query(sql2,new ArrayListHandler());
            System.out.println("需求2: 查询每个员工的 姓名, 薪资 和 所属部门名称");
            for(Object[] o : list2){
                System.out.println(Arrays.toString(o));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
